﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    public class FailureInfoManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();
        List<Tbl_FailureInfo> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_FailureInfo> returnlist = new List<Tbl_FailureInfo>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_FailureInfo
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    en_FailureName = dr["en_FailureName"] == DBNull.Value ? "" : dr["en_FailureName"].ToString(),
                    FailureName = dr["FailureName"] == DBNull.Value ? "" : dr["FailureName"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString(),
                    FailureNo = dr["FailureNo"] == DBNull.Value ? "" : dr["FailureNo"].ToString()

                });
            }
            return returnlist;
        }

        public List<Tbl_FailureInfo> GetObjListByCondition(Tbl_FailureInfo obj, int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by fi.Id desc) recordId,fi.*
            ,pt.PartName,pt.en_PartName
            from Tbl_FailureInfo fi 
            left join Tbl_Part pt on fi.PartNo=pt.PartNo where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.FailureName))
                    sql = sql.AppendFormat(@" and FailureName like '%{0}%'", obj.FailureName);
                if (!string.IsNullOrEmpty(obj.en_FailureName))
                    sql = sql.AppendFormat(@" and en_FailureName like '%{0}%'", obj.en_FailureName);
                if (!string.IsNullOrEmpty(obj.PartNo))
                    sql = sql.AppendFormat(@" and fi.PartNo like '%{0}%'", obj.PartNo);
                if (!string.IsNullOrEmpty(obj.PartName))
                    sql = sql.AppendFormat(@" and pt.PartName like '%{0}%'", obj.PartName);
                if (!string.IsNullOrEmpty(obj.en_PartName))
                    sql = sql.AppendFormat(@" and pt.en_PartName like '%{0}%'", obj.en_PartName);
            }
            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select * from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            return ConvertTableToObjs(dt);
        }

        public int GetObjListByConditionCount(Tbl_FailureInfo obj)
        {
            StringBuilder sql = new StringBuilder(@"select count(fi.Id) allCount 
            from Tbl_FailureInfo fi 
            left join Tbl_Part pt on fi.PartNo=pt.PartNo where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.FailureName))
                    sql = sql.AppendFormat(@" and FailureName like '%{0}%'", obj.FailureName);
                if (!string.IsNullOrEmpty(obj.en_FailureName))
                    sql = sql.AppendFormat(@" and en_FailureName like '%{0}%'", obj.en_FailureName);
                if (!string.IsNullOrEmpty(obj.PartNo))
                    sql = sql.AppendFormat(@" and fi.PartNo like '%{0}%'", obj.PartNo);
                if (!string.IsNullOrEmpty(obj.PartName))
                    sql = sql.AppendFormat(@" and pt.PartName like '%{0}%'", obj.PartName);
                if (!string.IsNullOrEmpty(obj.en_PartName))
                    sql = sql.AppendFormat(@" and pt.en_PartName like '%{0}%'", obj.en_PartName);
            }
            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public bool SaveObj(Tbl_FailureInfo cuObj)
        {
            string sql = "";
            if (cuObj.Id == 0)
                sql = string.Format(@"INSERT INTO Tbl_FailureInfo
           (FailureNo,PartNo,en_FailureName,FailureName,Remark,FC)
            VALUES('{0}','{1}','{2}','{3}','{4}','{5}')", cuObj.FailureNo, cuObj.PartNo, cuObj.en_FailureName,
                                                        cuObj.FailureName, cuObj.Remark, cuObj.FC);

            else sql = string.Format(@"UPDATE Tbl_FailureInfo
               SET  FailureNo='{0}'
                 ,PartNo ='{1}'
                 ,en_FailureName ='{2}'
                 ,FailureName ='{3}'
                 ,Remark ='{4}'
                 ,FC ='{5}'
             WHERE Id={6}", cuObj.FailureNo, cuObj.PartNo,
                          cuObj.en_FailureName, cuObj.FailureName, cuObj.Remark, cuObj.FC, cuObj.Id);
            return accessHelper.ExcultData(sql);
        }

        public Tbl_FailureInfo GetObjById(int objId)
        {
            Tbl_FailureInfo cur = new Tbl_FailureInfo();
            var sql = string.Format(@"select * from Tbl_FailureInfo where Id={0}", objId);
            var dt = accessHelper.ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                cur = new Tbl_FailureInfo
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    //en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    //PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    FailureNo = dr["FailureNo"] == DBNull.Value ? "" : dr["FailureNo"].ToString(),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    en_FailureName = dr["en_FailureName"] == DBNull.Value ? "" : dr["en_FailureName"].ToString(),
                    FailureName = dr["FailureName"] == DBNull.Value ? "" : dr["FailureName"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString(),
                    FC = dr["FC"] == DBNull.Value ? "" : dr["FC"].ToString()
                };
            }
            return cur;
        }

        public bool DeleteCuObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_FailureInfo where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

        public List<Tbl_FailureInfo> GetObjListByPartNo(string partNo, string failName, string en_failName, int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by Id desc) recordId,
                (select count(0) from Tbl_FailureRecord fr where fr.FailureId=fi.Id and FailureLevel=0) FailureDCount,
                (select count(0) from Tbl_FailureRecord fr where fr.FailureId=fi.Id and FailureLevel=1) FailureZCount,
                (select count(0) from Tbl_FailureRecord fr where fr.FailureId=fi.Id and FailureLevel=2) FailureGCount,
                fi.* from Tbl_FailureInfo fi where 1=1");

            if (!string.IsNullOrEmpty(partNo))
                sql = sql.AppendFormat(@" and PartNo = '{0}'", partNo);
            if (!string.IsNullOrEmpty(failName))
                sql = sql.AppendFormat(@" and (Upper(FailureName) like '%{0}%' or Upper(en_FailureName) like '%{1}%')", failName.ToUpper(), failName.ToUpper());

            //if (!string.IsNullOrEmpty(en_failName))
            //    sql = sql.AppendFormat(@" and en_FailureName like '%{0}%'", en_failName);

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select * from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            List<Tbl_FailureInfo> returnlist = new List<Tbl_FailureInfo>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_FailureInfo
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    //en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    //PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    FailureNo = dr["FailureNo"] == DBNull.Value ? "" : dr["FailureNo"].ToString(),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    en_FailureName = dr["en_FailureName"] == DBNull.Value ? "" : dr["en_FailureName"].ToString(),
                    FailureName = dr["FailureName"] == DBNull.Value ? "" : dr["FailureName"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString(),
                    FailureZCount = dr["FailureZCount"] == DBNull.Value ? 0 : Int32.Parse(dr["FailureZCount"].ToString()),
                    FailureDCount = dr["FailureDCount"] == DBNull.Value ? 0 : Int32.Parse(dr["FailureDCount"].ToString()),
                    FailureGCount = dr["FailureGCount"] == DBNull.Value ? 0 : Int32.Parse(dr["FailureGCount"].ToString()),
                    FC = dr["FC"] == DBNull.Value ? "" : dr["FC"].ToString()
                });
            }
            return returnlist;
        }

        public int GetObjListByPartNoCount(string partNo, string failName, string en_failName)
        {
            StringBuilder sql = new StringBuilder("select count(0) allCount from Tbl_FailureInfo where 1=1");

            if (!string.IsNullOrEmpty(partNo))
                sql = sql.AppendFormat(@" and PartNo = '{0}'", partNo);
            if (!string.IsNullOrEmpty(failName))
                sql = sql.AppendFormat(@" and (Upper(FailureName) like '%{0}%' or Upper(en_FailureName) like '%{1}%')", failName.ToUpper(), failName.ToUpper());

            //if (!string.IsNullOrEmpty(failName))
            //    sql = sql.AppendFormat(@" and FailureName like '%{0}%'", failName);

            //if (!string.IsNullOrEmpty(en_failName))
            //    sql = sql.AppendFormat(@" and en_FailureName like '%{0}%'", en_failName);

            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public List<Tbl_PartSta_Relation> GetPartStationListByConditon(string stationIds)
        {
            StringBuilder sql = new StringBuilder(@"select distinct tpr.StationId,tpr.PartNo,pt.PartName,pt.en_PartName 
            from Tbl_Part pt  
            left join Tbl_PartSta_Relation tpr on tpr.PartNo=pt.PartNo 
            where 1=1 ");

            if (!string.IsNullOrEmpty(stationIds))
                sql = sql.AppendFormat(@" and tpr.StationId in ({0})", stationIds);
            sql.Append(@" order by pt.en_PartName,pt.PartName");
            var dt = accessHelper.ExcuteDataTable(sql.ToString());

            List<Tbl_PartSta_Relation> returnlist = new List<Tbl_PartSta_Relation>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_PartSta_Relation
                {
                    StationId = dr["StationId"] == DBNull.Value ? 0 : Int32.Parse(dr["StationId"].ToString()),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString()
                });
            }
            return returnlist;
        }

    }
}
